package com.javaweb.common.utils;

import com.javaweb.common.config.UploadFileConfig;
import org.springframework.beans.factory.annotation.Value;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DBUtils {
    private static Connection conn = null;
    private static DBUtils instance;


    private DBUtils() {
    }

    static {
        instance = new DBUtils();

    }

    public static Connection getConnection() throws Exception {
        Class.forName(UploadFileConfig.driver);
        Connection connection = DriverManager.getConnection(UploadFileConfig.url, UploadFileConfig.username, UploadFileConfig.password);
        return connection;
    }
    public static void SetConnection(Connection connection) {
        //conn = connection;
    }

    //执行sql查询
    public static  void ExecNonQuery(String sql) throws SQLException {
        try {
            conn = getConnection();
            Statement statement = conn.createStatement();
            statement.closeOnCompletion();
            statement.execute(sql);
        } catch(SQLException ex)
        {
            throw ex;
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public static  ResultSet GetResultSet(String sql) throws SQLException {
        ResultSet rs=null;
        try {
            conn = getConnection();
            Statement statement = conn.createStatement();
            statement.closeOnCompletion();
            rs =statement.executeQuery(sql);
        } catch(SQLException ex){
            throw ex;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return rs;
    }

    public static String GetQuotationNo(String billtype,String projecttype,String producttye,int creater) {
        String sql = "{call P_BD_AUTOCODE_INQ(?,?,?,?,?,?)}";
        String code = "";
        CallableStatement call = null;
        try {
            conn = getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            call = conn.prepareCall(sql);
            call.closeOnCompletion();
            //赋值
            call.setString(1, billtype);
            call.setString(2, projecttype);
            call.setString(3, producttye);
            call.setInt(4, creater);
            //对于out参数，声明
            call.registerOutParameter(5, Types.VARCHAR);
            call.registerOutParameter(6, Types.VARCHAR);

            //调用
            call.execute();

            //取值
            code = call.getString(5);
            String msg = call.getString(6);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
            code = "E_"+throwables.getMessage();
        }
        return code;

    }

    public static String GetBillCode(String billtype) {
        String sql = "{call P_BD_AUTOCODE(?,?,?)}";
        String msg = "";
        CallableStatement call = null;
        try {
            conn = getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            call = conn.prepareCall(sql);
            call.closeOnCompletion();
            //赋值
            call.setString(1, billtype);
            //对于out参数，声明
            call.registerOutParameter(2, Types.VARCHAR);
            call.registerOutParameter(3, Types.VARCHAR);

            //调用
            call.execute();

            //取值
            msg = call.getString(2);
            //String msg = call.getString(3);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
            msg = throwables.getMessage();
        }
        return msg;

    }



    public static String GetSuppScore(Integer pkSupplier,String procName) {
        String sql = "{call "+procName+"(?,?)}";
        String message = "";
        CallableStatement call = null;
        try {
            conn = getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            call = conn.prepareCall(sql);
            call.closeOnCompletion();
            //赋值
            call.setInt(1, pkSupplier);
            //对于out参数，声明
            call.registerOutParameter(2, Types.VARCHAR);

            //调用
            call.execute();

            //取值
            message = call.getString(2);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
            message=throwables.getMessage();
        }
        return message;
    }

    public static List convertList(ResultSet rs) throws SQLException {
        List list = new ArrayList();
        ResultSetMetaData md = rs.getMetaData();
        int columnCount = md.getColumnCount(); //Map rowData;
        while (rs.next()) { //rowData = new HashMap(columnCount);
            Map rowData = new HashMap();
            for (int i = 1; i <= columnCount; i++) {
                rowData.put(md.getColumnName(i), rs.getObject(i));
            }
            list.add(rowData);
        }
        return list;
    }
}
